/*
	SELECT * FROM FN_CALCULATE_WORK_WEEK ( '12/31/2008' )
*/
ALTER FUNCTION FN_CALCULATE_WORK_WEEK ( @DATE_TO_FIND_WW DATETIME )
 RETURNS @RESULTS TABLE ( WW_NUMBER INT, WW_QTR INT )
AS
BEGIN
	DECLARE @WW_NO INT
	DECLARE @WW_QTR INT
	DECLARE @NO_OF_DAYS_INCLUDING_END_DATE INT
	DECLARE @WW_START_DATE DATETIME
	DECLARE @WW_YEAR INT
	IF ( @DATE_TO_FIND_WW IS NOT NULL AND LTRIM(RTRIM(@DATE_TO_FIND_WW)) != '' )
	 BEGIN
		SET @WW_YEAR = DATEPART(YYYY, @DATE_TO_FIND_WW)
		--SELECT @WW_START_DATE = CC_START_DATE FROM COMPANY_CALENDAR WHERE CC_YEAR = @WW_YEAR
		SELECT @WW_START_DATE = MAX(WW_START_DATE) FROM COMPANY_CALENDAR WHERE WW_START_DATE <= @DATE_TO_FIND_WW
		IF ( @WW_START_DATE IS NULL ) --COMPANY CALENDAR IS NOT SET FOR THE YEAR
		 BEGIN
			--DEFAULT SETTING TO JAN 1st AS THE WORK WEEK START DATE
			SET @WW_START_DATE = CAST ( '01/01/' + CONVERT(VARCHAR, @WW_YEAR) AS DATETIME )
		 END
		/*ELSE IF ( @DATE_TO_FIND_WW < @WW_START_DATE )
		 BEGIN
			--WORK WEEK START DATE SHOULD BE CONSIDERED FOR THE PREVIOUS YEAR
			SET @WW_YEAR = @WW_YEAR - 1
			SELECT @WW_START_DATE = CC_START_DATE FROM COMPANY_CALENDAR WHERE CC_YEAR = @WW_YEAR
			IF ( @WW_START_DATE IS NULL ) --COMPANY CALENDAR IS NOT SET FOR THE YEAR
			 BEGIN
				--DEFAULT SETTING TO JAN 1st AS THE WORK WEEK START DATE
				SET @WW_START_DATE = CAST ( ('01/01/' + @WW_YEAR) AS DATETIME )
			 END
		 END*/
		
		SET @NO_OF_DAYS_INCLUDING_END_DATE = DATEDIFF( DD, @WW_START_DATE, @DATE_TO_FIND_WW )+1
		SET @WW_NO = @NO_OF_DAYS_INCLUDING_END_DATE/7
		IF ( (@NO_OF_DAYS_INCLUDING_END_DATE % 7) > 0 )
		 BEGIN
			SET @WW_NO = @WW_NO + 1
		 END

		SET @WW_NO = @WW_NO%53
		IF ( @WW_NO = 0 )
		 BEGIN
			SET @WW_NO = 53
		 END

		SELECT @WW_QTR = CQC_QUARTER_NO FROM Company_Quarterly_Calendar WHERE @DATE_TO_FIND_WW >= CQC_START_DATE AND @DATE_TO_FIND_WW <= CQC_END_DATE
		
		IF ( @WW_QTR IS NULL )
		BEGIN
			IF ( @WW_NO <= 13 )
			 BEGIN
				SET @WW_QTR = 1
			 END
			ELSE IF ( @WW_NO <= 26 )
			 BEGIN
				SET @WW_QTR = 2
			 END
			ELSE IF ( @WW_NO <= 39 )
			 BEGIN
				SET @WW_QTR = 3
			 END
			ELSE
			 BEGIN
				SET @WW_QTR = 4
			 END
		END
	 END	
	INSERT INTO @RESULTS VALUES ( @WW_NO, @WW_QTR )
 RETURN
END	
